﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using DevExpress.Web.ASPxEditors;
using DevExpress.Web.ASPxFormLayout;
using DevExpress.Web.ASPxGridView;
using System.Collections;
using DevExpress.Web.ASPxEditors;
using FirebirdSql.Data.FirebirdClient;
using Dez;
using DispatcherWebAppModel;

public partial class Default2 : System.Web.UI.Page
{
    DispatcherWebAppEntities context = new DispatcherWebAppEntities();
    DEZ dezContext = new DEZ();

    SqlConnection sqlCn = new SqlConnection();
    int DetailId;
    DataSet ds = null;
    int HouseID, SuonHouseId;

    private static DbConn GetDb()
    {
        DbConn db = new DbConn();
        return db;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack && !IsCallback)
        {
            Session["p_Update"] = Server.UrlEncode(System.DateTime.Now.ToString());
            int WORKLIST = SearchOrder();


            TabbedLayoutGroup group = (TabbedLayoutGroup)grp_root.Items[0];
            //if (WORKLIST < 0) { }
            group.PageControl.ActiveTabIndex = 0;
            if (Request.QueryString["code"] != null)
            {
                DetailId = Convert.ToInt32(Request.QueryString["code"]);
                Session["DetailId"] = DetailId;
                GetDetailData(DetailId);
                CreateDataSet();
                // 
                FillServisWork(cbb_work);
                //допустимый период
                DateTime d1, d2;
                GetAttirePeriod(out d1, out d2);
                dt1.MinDate = d1;
                dt1.MaxDate = d2;
                // код дома суон
                SuonHouseId = GetSuonHouse(HouseID);
                Session["SuonHouseID"] = SuonHouseId;
                lbl_period.Text = String.Format("допустимый период с {0:dd.MM.yyyy} по {1:dd.MM.yyyy}", d1.Date, d2.Date);
            }
        }
        else
        {
            ds = (DataSet)Session["DataSet"];
            HouseID = (int)Session["HouseID"];
            SuonHouseId = (int)Session["SuonHouseID"];
            DetailId = (int)Session["DetailId"];
        }
        grd_Mat.DataSource = ds.Tables[0];
        grd_Mat.DataBind();
    }

    protected void Page_PreRender(object sender, EventArgs e)
    {
        ViewState["p_Update"] = Session["p_Update"];
    }

    protected void CreateDataSet()
    {
        ds = new DataSet();
        DataTable matTable = new DataTable();
        matTable.Columns.Add("ID", typeof(Int32));
        matTable.Columns.Add("Code", typeof(Int32));
        matTable.Columns.Add("Quantity", typeof(double)).AllowDBNull = true;
        matTable.Columns.Add("Ed", typeof(string)).AllowDBNull = true;
        matTable.Columns.Add("Price", typeof(double)).AllowDBNull = true;
        matTable.PrimaryKey = new DataColumn[] { matTable.Columns["ID"] };

        ds.Tables.Add(matTable);
        Session["DataSet"] = ds;
    }

    protected void GetDetailData(int id)
    {
        sqlCn.ConnectionString = context.Connection.ConnectionString;
        sqlCn.Open();

        SqlDataReader rdr = null;
        using (SqlCommand cmd = new SqlCommand("Rq_Request_GetRequestById", sqlCn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            // Input param.
            SqlParameter param = new SqlParameter("@RequestId", id);
            param.SqlDbType = SqlDbType.Int;
            ///param.Value = id;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);

            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                HouseID = (int)rdr["Houseid"];
                Session["HouseID"] = HouseID;
                lb_RqId.Value = id;
                lbl_MgmtCompanyId.Value = rdr["MgmtCompany_Title"];
                lbl_DU.Value = rdr["HouseCompany_Title"];
                lbl_LocalId.Value = rdr["LocalId"];
                lbl_State.Value = GetStatusName(Convert.ToInt32(rdr["Status"]));
                lbl_Addr.Value = GetAddr(rdr);
                // next group
                lbl_UserIn.Value = GetUserIn(rdr);
                if (rdr["CreatedAtUtc"] != System.DBNull.Value)
                    lbl_DateCreate.Value = Convert.ToDateTime(rdr["CreatedAtUtc"]).ToLocalTime();
                if (rdr["IssuedAtUtc"] != System.DBNull.Value)
                    lbl_DateIn.Value = Convert.ToDateTime(rdr["IssuedAtUtc"]).ToLocalTime();
                if (rdr["DueDateUtc"] != System.DBNull.Value)
                    lbl_DueDate.Value = Convert.ToDateTime(rdr["DueDateUtc"]).ToLocalTime();
                if (rdr["CompletedAtUtc"] != System.DBNull.Value)
                    lbl_DateComplet.Value = Convert.ToDateTime(rdr["CompletedAtUtc"]).ToLocalTime();
                if (rdr["ClosedAtUtc"] != System.DBNull.Value)
                    lbl_isClosed.Value = Convert.ToDateTime(rdr["ClosedAtUtc"]).ToLocalTime();
                if (rdr["WorkCompany_Title"] != System.DBNull.Value)
                    lbl_WorkCompany.Value = rdr["WorkCompany_Title"];
                lbl_RequesterName.Value = rdr["RequesterName"];
                lbl_RequesterPhone.Value = rdr["RequesterPhone"];
                lbl_JobType.Value = GetJobTitle(rdr);
                if (rdr["Location"] != System.DBNull.Value)
                    lbl_Loc.Value = GetLocation(Convert.ToInt32(rdr["Location"]));
                if ((bool)rdr["IsEmergency"] == false)
                { lbl_IsEmergency.Value = "Нет"; }
                else
                { lbl_IsEmergency.Value = "Да"; }

                if ((bool)rdr["IsPaid"] == false)
                { lbl_IsPaid.Value = "Нет"; }
                else
                { lbl_IsPaid.Value = "Да"; }
                lbl_ExecutorName.Value = rdr["ExecutorName"];
            }

        }
        rdr.Close();

    }

    public string GetStatusName(int id)
    {
        switch (id)
        {
            case 1:
                return "Не рассмотрена";
            case 2:
                return "В процессе";
            case 3:
                btn_Exec.Visible = false;
                return "Выполнена";
            case 4:
                return "Возвращена";
            case 5:
                return "Снята";
            case 6:
                return "Закрыта";
            case 7:
                return "Отложена";
            case 8:
                return "Текущий ремонт";
            case 9:
                return "Запрос в службу эксплуатации (СЭ";
            default:
                return "";
        }
    }

    public string GetAddr(SqlDataReader sdr)
    {
        string addr = string.Empty;
        if (sdr["Street_Title"] != System.DBNull.Value)
            addr += sdr["Street_Title"];
        if (sdr["House_Title"] != System.DBNull.Value)
            addr += ", " + sdr["House_Title"];
        if (sdr["Apartment"] != System.DBNull.Value)
            addr += ", кв. " + sdr["Apartment"];
        if (sdr["Entrance"] != System.DBNull.Value)
            addr += ", " + sdr["Entrance"] + " подъезд";
        if (sdr["RequesterPhone"] != System.DBNull.Value)
            addr += ", телефон: " + sdr["RequesterPhone"];
        return addr;

    }

    public string GetUserIn(SqlDataReader sdr)
    {
        string UserIn = string.Empty;
        if (sdr["CreatedBy_LastName"] != System.DBNull.Value)
            UserIn += sdr["CreatedBy_LastName"]+" ";
        if (sdr["CreatedBy_FirstName"] != System.DBNull.Value)
            UserIn += ((string)sdr["CreatedBy_FirstName"])[0] + ".";
        if (sdr["CreatedBy_MiddleName"] != System.DBNull.Value)
            UserIn += ((string)sdr["CreatedBy_MiddleName"])[0] + ".";
        if (sdr["CreatedBy_Company_Title"] != System.DBNull.Value)
            UserIn += " (" + sdr["CreatedBy_Company_Title"] + ")";
        return UserIn;
    }

    public string GetJobTitle(SqlDataReader sdr)
    {
        string JobTitle = string.Empty;
        if (sdr["JobType_Title"] != System.DBNull.Value)
            JobTitle += sdr["JobType_Title"];
        if (sdr["JobSubtype_Title"] != System.DBNull.Value)
            JobTitle += " / " + sdr["JobSubtype_Title"];
        return JobTitle;
    }

    public string GetLocation(int id)
    {
        switch (id)
        {
            case 1:
                return "Дом";
            case 2:
                return "Квартира";
            case 3:
                return "Подъезд";
            case 4:
                return "Лестничная клетка";
            case 5:
                return "Крыша";
            case 6:
                return "Другое";
            case 7:
                return "Подвал";
            case 8:
                return "Мусоропровод";
            case 9:
                return "Территория";
            case 10:
                return "Карман";
            default:
                return "";
        }
    }

    protected void grd_Mat_RowInserting(object sender, DevExpress.Web.Data.ASPxDataInsertingEventArgs e)
    {
        ds = (DataSet)Session["DataSet"];
        ASPxGridView gridView = (ASPxGridView)sender;
        DataTable dataTable = gridView.SettingsDetail.IsDetailGrid ? ds.Tables[1] : ds.Tables[0];
        DataRow row = dataTable.NewRow();
        e.NewValues["ID"] = GetNewId();

        if (e.NewValues["Quantity"] == null)
            e.NewValues["Quantity"] = 0;
        if (e.NewValues["Price"] == null)
            e.NewValues["Price"] = 0;

        IDictionaryEnumerator enumerator = e.NewValues.GetEnumerator();
        enumerator.Reset();
        while (enumerator.MoveNext())
            if (enumerator.Key.ToString() != "Count")
                row[enumerator.Key.ToString()] = enumerator.Value;
        gridView.CancelEdit();
        e.Cancel = true;
        dataTable.Rows.Add(row);
    }

    private int GetNewId()
    {
        ds = (DataSet)Session["DataSet"];
        DataTable table = ds.Tables[0];
        if (table.Rows.Count == 0) return 0;
        int max = Convert.ToInt32(table.Rows[0]["ID"]);
        for (int i = 1; i < table.Rows.Count; i++)
        {
            if (Convert.ToInt32(table.Rows[i]["ID"]) > max)
                max = Convert.ToInt32(table.Rows[i]["ID"]);
        }
        return max + 1;
    }
    protected void grd_Mat_RowDeleting(object sender, DevExpress.Web.Data.ASPxDataDeletingEventArgs e)
    {
        ASPxGridView gridView = (ASPxGridView)sender;

        int i = gridView.FindVisibleIndexByKeyValue(e.Keys[gridView.KeyFieldName]);
        //Control c = gridView.FindDetailRowTemplateControl(i, "ASPxGridView2");
        e.Cancel = true;
        ds = (DataSet)Session["DataSet"];
        ds.Tables[0].Rows.Remove(ds.Tables[0].Rows.Find(e.Keys[gridView.KeyFieldName]));
    }

    protected void FillServisWork(ASPxComboBox combo)
    {
        combo.DataSourceID = "ds_serviswork";
        combo.ValueField = "CODE";
        combo.TextField = "NAME";
        combo.DataBindItems();

        //combo.Items.Insert(0, new ListEditItem("[ВСЕ]", null)); // Null Item
    }

    protected void GetAttirePeriod(out DateTime d1, out DateTime d2)
    {
        d1 = DateTime.Now;
        d2 = DateTime.Now;
        #region если текущая дата меньше 26 то это месяц закрытия наряда
        //и поэтому ищем наряд за период с 25 предыдущего месяца по
        //26 текущего месяца 
        #endregion
        if (DateTime.Now.Day < 26)
        {
            DateTime d_temp = DateTime.Now.AddMonths(-1);
            d1 = new DateTime(d_temp.Year, d_temp.Month, 25);
            d2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 26);

        }
        else
        {
            #region если текущая дата больше или равна 26 то это дата начала нового периода
            /*и поэтому ищем наряд с 26 текущего месяца по 25 следующего месяца*/
            #endregion
            DateTime d_temp = DateTime.Now.AddMonths(1);
            d1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 26);
            d2 = new DateTime(d_temp.Year, d_temp.Month, 25);
        }
    }

    #region получаем код дома из суон по коду дома из диспетчера
    protected int GetSuonHouse(int DispHouse)
    {
        DEZ dez = new DEZ();

        int SuonHouse = -1;
        if (DispHouse == null) { return SuonHouse; }
        else
        {
            //FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
            //csb.UserID = "SYSDBA";
            //csb.Password = "masterkey";
            //csb.Database = @"localhost:f:\base\DEZ.FDB";
            DbConn db = GetDb();
            FbConnection fbBD = db.FbDB;
            fbBD.Open();

            FbDataReader rdr = null;
            using (FbCommand cmd = new FbCommand("SELECT CODE FROM HOUSE WHERE DISP_ID=@COD", fbBD))
            {
                // Input param.
                FbParameter param = new FbParameter("@COD", DispHouse);
                param.FbDbType = FbDbType.Integer;
                param.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(param);

                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (rdr["CODE"] == null)
                    { SuonHouse = -1; }
                    else
                    {
                        SuonHouse = (int)rdr["CODE"];
                    }
                }
                rdr.Close();
            }
            fbBD.Close();

            //Entities dez = new Entities();
            //SqlConnection dezCon = new SqlConnection();
            //dezCon.ConnectionString =  dez.Database.Connection.ConnectionString;
            //dezCon.Open();

            //SqlDataReader rdr = null;
            //using (SqlCommand cmd = new SqlCommand("SELECT CODE FROM HOUSE WHERE DISP_ID=:COD", dezCon))
            //{
            //    // Input param.
            //    SqlParameter param = new SqlParameter("@COD", DispHouse);
            //    param.SqlDbType = SqlDbType.Int;
            //    param.Direction = ParameterDirection.Input;
            //    cmd.Parameters.Add(param);

            //    rdr = cmd.ExecuteReader();
            //    while (rdr.Read())
            //    {
            //        if (rdr["CODE"] == null)
            //        { SuonHouse = -1; }
            //        else 
            //        { SuonHouse = (int)rdr["CODE"]; }                   
            //    }
            //    rdr.Close();                
            //}
            return SuonHouse;
        }
    }
    #endregion

    protected void InsertWork()
    {
        int CODE = -1; //код новой записи

        DateTime d1 = new DateTime(dt1.Date.Year, dt1.Date.Month, dt1.Date.Day, tm1.DateTime.Hour, tm1.DateTime.Minute, 0);
        int House = GetSuonHouse(HouseID);
        int WORKLIST = SearchOrder();
        if (WORKLIST < 0) return; //Выдать сообщение об ошибке!!!!!!!!!

        //grd_detail.Selection.SelectRow(0);
        string DISP_NOTE = (string)grd_detail.GetRowValues(0, "NOTE");

        DbConn db = GetDb();
        FbConnection fbBD = db.FbDB;
        fbBD.Open();

        //FbDataReader rdr = null;
        using (FbCommand cmd = fbBD.CreateCommand())
        {
            cmd.CommandText = "WORKLIST$INSERT";
            cmd.CommandType = CommandType.StoredProcedure;
            // Input param.
            cmd.Parameters.Add("@DATA", FbDbType.Date).Value = dt1.Date;
            cmd.Parameters.Add("@KONTRAGENT", FbDbType.Integer).Value = Profile.UserInfo.ActiveKontragent;
            cmd.Parameters.Add("@SERVISWORK", FbDbType.Integer).Value = cbb_work.Value;
            cmd.Parameters.Add("@HOUSE", FbDbType.Integer).Value = House;
            cmd.Parameters.Add("@PRICE", FbDbType.Numeric).Value = spn_price.Value;
            cmd.Parameters.Add("@NOTE", FbDbType.VarChar, 1024).Value = mmo_Note.Text;
            cmd.Parameters.Add("@WORKLIST", FbDbType.Integer).Value = WORKLIST;
            cmd.Parameters.Add("@QUANTITY", FbDbType.Numeric).Value = spn_col.Value;
            cmd.Parameters.Add("@REQUESTID", FbDbType.Integer).Value = DetailId;
            cmd.Parameters.Add("@DISP_BASE", FbDbType.VarChar, 512).Value = DISP_NOTE;
            // output peram
            CODE = (int)cmd.ExecuteScalar();
        }
        fbBD.Close();
        // создаем работы
        DataTable table = ds.Tables[0];
        if (table.Rows.Count > 0)
        {
            int max = Convert.ToInt32(table.Rows[0]["ID"]);
            for (int i = 0; i < table.Rows.Count; i++)
            {
                InsertMaterial(CODE,
                               Profile.UserInfo.ActiveKontragent,
                               House,
                               (double)table.Rows[i]["Quantity"],
                               (double)table.Rows[i]["Price"],
                               (int)table.Rows[i]["CODE"]);
            }
        }
        // выполняем заявку
        CreateComment();
        //
        //TabbedLayoutGroup group = (TabbedLayoutGroup)grp_root.Items[0];
        //group.PageControl.ActiveTabIndex = 0;
        //btn_Exec.Enabled = false;
        // clear parametrs
        //ClearControls();
        
        //GetDetailData(DetailId);
    }

    protected void ClearControls()
    {
        cbb_work.Value = null;
        spn_col.Value = 0;
        spn_price.Value = 0;
        mmo_Note.Value = null;
    }

    protected void InsertMaterial(int WorkSpecList, int kontr, int House, double Quantity, double Price, int ClassMat)
    {
        DbConn db = GetDb();
        FbConnection fbBD = db.FbDB;
        fbBD.Open();
        using (FbCommand cmd = fbBD.CreateCommand())
        {
            cmd.CommandText = "insert into WORKSPECMATERIAL (WORKSPECLIST, DATA, KONTRAGENT, SERVISWORK, HOUSE, QUANTITY, PRICE, CLASSMAT)" +
                              " values (@WORKSPECLIST, @DATA, @KONTRAGENT, @SERVISWORK, @HOUSE,  @QUANTITY, @PRICE, @CLASSMAT)";
            //cmd.CommandType = CommandType.StoredProcedure;
            // Input param.
            cmd.Parameters.Add("@WORKSPECLIST", FbDbType.Integer).Value = WorkSpecList;
            cmd.Parameters.Add("@DATA", FbDbType.Date).Value = DateTime.Now;
            cmd.Parameters.Add("@KONTRAGENT", FbDbType.Integer).Value = kontr;
            cmd.Parameters.Add("@SERVISWORK", FbDbType.Integer).Value = cbb_work.Value;
            cmd.Parameters.Add("@HOUSE", FbDbType.Integer).Value = House;

            cmd.Parameters.Add("@QUANTITY", FbDbType.Numeric).Value = Quantity;
            cmd.Parameters.Add("@PRICE", FbDbType.Numeric).Value = Price;
            cmd.Parameters.Add("@CLASSMAT", FbDbType.Integer).Value = ClassMat;

            // output peram
            cmd.ExecuteNonQuery();
        }
        fbBD.Close();
    }

    /// <summary>
    /// поиск активного наряда
    /// возвращает код активного наряда (-1 если не нашли)
    /// </summary>
    /// <returns></returns>
    protected int SearchOrder()
    {
        int CODE = -1;
        DateTime d1, d2;
        GetAttirePeriod(out d1, out d2);
        //
        if (SuonHouseId == 0)
        {
            SuonHouseId = GetSuonHouse(HouseID);
        }
        //
        DbConn db = GetDb();
        FbConnection fbBD = db.FbDB;
        fbBD.Open();

        FbDataReader rdr = null;
        using (FbCommand cmd = new FbCommand("select CODE from WORKLIST where DATA between @d1 and @d2 and HOUSE = @house and KONTRAGENT = @kontr", fbBD))
        {
            FbParameter param;
            // Input param.
            param = new FbParameter("@d1", d1);
            param.FbDbType = FbDbType.Date;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);

            param = new FbParameter("@d2", d2);
            param.FbDbType = FbDbType.Date;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);

            param = new FbParameter("@house", SuonHouseId);
            param.FbDbType = FbDbType.Integer;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);

            param = new FbParameter("@kontr", Profile.UserInfo.ActiveKontragent);
            param.FbDbType = FbDbType.Integer;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);

            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                if (rdr["CODE"] != null)
                { CODE = (int)rdr["CODE"]; }
            }
            rdr.Close();
        }
        fbBD.Close();
        return CODE;
    }

    protected void btn_Exec_Click(object sender, EventArgs e)
    {
        if (Session["p_Update"].ToString() == ViewState["p_Update"].ToString())
        {
            InsertWork();
            Response.Redirect("~/So/detail.aspx?code=" + DetailId.ToString());
            Session["p_Update"] = Server.UrlEncode(System.DateTime.Now.ToString());
        }
        else
        {
            TabbedLayoutGroup group = (TabbedLayoutGroup)grp_root.Items[0];
            group.PageControl.ActiveTabIndex = 0;
            btn_Exec.Enabled = false;
            ClearControls();
        }
    }

    /// <summary>
    /// возвращаем список материалов ввиде строки
    /// </summary>
    /// <returns></returns>
    protected string GetMaterials()
    {
        string s = string.Empty;
        DataTable table = ds.Tables[0];
        if (table.Rows.Count > 0)
        {
            for (int i = 0; i < table.Rows.Count; i++)
            {
                int code = (int)table.Rows[i]["Code"];
                var query = (from m in dezContext.CLASSMAT
                             where m.CODE == code
                             select m).First();
                s = " Материалы: ";
                if (i < table.Rows.Count)
                {
                    s += string.Format("{0}, ", query.FULLNAME);
                }
                else { s += string.Format("{0}. ", query.FULLNAME); }
            }
        }
        return s;
    }

    protected object GetValue(object value)
    {
        if (value == null)
        { return DBNull.Value; }
        else { return value; }
    }

    protected void CreateComment()
    {
        DateTime d1 = new DateTime(dt1.Date.Year, dt1.Date.Month, dt1.Date.Day, tm1.DateTime.Hour, tm1.DateTime.Minute, 0);
        string CommentBody;
        if (chk_AddWork.Checked)
        {
            CommentBody = String.Format("Вид работы: {0}, {1} {2}", cbb_work.Text, GetMaterials(), mmo_Note.Text);
        }
        else { CommentBody = CommentBody = String.Format("{0} {1}", GetMaterials(), mmo_Note.Text); }
        

        var query = (from r in context.Rq_Request
                     where r.RequestId == DetailId
                     select r).First();

        int ABC = 0;
        if (chk_ABC.Checked) ABC = 1;

        DbConn db = GetDb();
        SqlConnection DispDB = db.DispDB;
        DispDB.Open();

        using (SqlCommand cmd = DispDB.CreateCommand())
        {
            cmd.CommandText = "Rq_Request_UpdateRequest";
            cmd.CommandType = CommandType.StoredProcedure;
            // Input param.
            cmd.Parameters.Add("@RequestId", SqlDbType.Int).Value = DetailId;
            cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 3; //выполнена
            cmd.Parameters.Add("@IssuedAtUtc", SqlDbType.SmallDateTime).Value = GetValue(query.IssuedAtUtc);
            cmd.Parameters.Add("@DueDateUtc", SqlDbType.SmallDateTime).Value = GetValue(query.DueDateUtc);
            cmd.Parameters.Add("@CompletedAtUtc", SqlDbType.SmallDateTime).Value = d1.ToUniversalTime();
            cmd.Parameters.Add("@CanceledByCompanyId", SqlDbType.Int).Value = GetValue(query.CanceledByCompanyId);

            cmd.Parameters.Add("@CanceledBy", SqlDbType.Int).Value = GetValue(query.CanceledBy);
            cmd.Parameters.Add("@ClosedByCompanyId", SqlDbType.Int).Value = GetValue(query.ClosedByCompanyId);
            cmd.Parameters.Add("@ClosedBy", SqlDbType.Int).Value = GetValue(query.ClosedBy);
            cmd.Parameters.Add("@ClosedAtUtc", SqlDbType.SmallDateTime).Value = GetValue(query.ClosedAtUtc);

            cmd.Parameters.Add("@VerifiedByCompanyId", SqlDbType.Int).Value = GetValue(query.VerifiedByCompanyId);
            cmd.Parameters.Add("@VerifiedBy", SqlDbType.Int).Value = GetValue(query.VerifiedBy);
            cmd.Parameters.Add("@VerifiedAtUtc", SqlDbType.SmallDateTime).Value = GetValue(query.VerifiedAtUtc);
            cmd.Parameters.Add("@RequesterName", SqlDbType.NVarChar, 50).Value = GetValue(query.RequesterName);
            cmd.Parameters.Add("@RequesterPhone", SqlDbType.NVarChar, 50).Value = GetValue(query.RequesterPhone);

            cmd.Parameters.Add("@Location", SqlDbType.SmallInt).Value = GetValue(query.Location);
            cmd.Parameters.Add("@JobTypeId", SqlDbType.SmallInt).Value = GetValue(query.JobTypeId);
            cmd.Parameters.Add("@JobSubtypeId", SqlDbType.SmallInt).Value = GetValue(query.JobSubtypeId);
            cmd.Parameters.Add("@HouseId", SqlDbType.Int).Value = GetValue(query.HouseId);
            cmd.Parameters.Add("@Entrance", SqlDbType.TinyInt).Value = GetValue(query.Entrance);
            cmd.Parameters.Add("@Floor", SqlDbType.TinyInt).Value = GetValue(query.Floor);

            cmd.Parameters.Add("@Apartment", SqlDbType.NVarChar, 50).Value = GetValue(query.Apartment);
            cmd.Parameters.Add("@AccessCode", SqlDbType.NVarChar, 20).Value = GetValue(query.AccessCode);
            cmd.Parameters.Add("@Extra", SqlDbType.NVarChar, 50).Value = GetValue(query.Extra);

            cmd.Parameters.Add("@IsEmergency", SqlDbType.Bit).Value = GetValue(query.IsEmergency);
            cmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = GetValue(query.Price);
            cmd.Parameters.Add("@RequestBody", SqlDbType.NVarChar, 20).Value = GetValue(query.RequestBody);

            cmd.Parameters.Add("@WorkCompanyId", SqlDbType.Int).Value = GetValue(query.WorkCompanyId);
            cmd.Parameters.Add("@ExecutorName", SqlDbType.NVarChar, 20).Value = GetValue(query.ExecutorName);
            cmd.Parameters.Add("@ExecutorPhoneNumber", SqlDbType.NVarChar, 20).Value = GetValue(query.ExecutorPhoneNumber);

            cmd.Parameters.Add("@CommentCreatedBy", SqlDbType.Int).Value = Profile.UserInfo.disp.UserId;
            cmd.Parameters.Add("@CommentCreatedAtUtc", SqlDbType.SmallDateTime).Value = d1.ToUniversalTime();
            cmd.Parameters.Add("@CommentIsSystem", SqlDbType.Bit).Value = 0;

            cmd.Parameters.Add("@CommentIsEmergency", SqlDbType.Bit).Value = ABC;

            cmd.Parameters.Add("@CommentChangeItemsXml", SqlDbType.NVarChar).Value = "<ChangeItems />";
            cmd.Parameters.Add("@CommentBody", SqlDbType.NVarChar).Value = CommentBody;
            cmd.Parameters.Add("@CommentChangeHistoryXml", SqlDbType.NVarChar).Value = "<CommentChangeHistory />";
            cmd.Parameters.Add("@IsKeyComment", SqlDbType.Bit).Value = 1;

            cmd.ExecuteNonQuery();
        }
        DispDB.Close();
    }
}